package com.vf.admin.common.db;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.vf.admin.common.db.SqlUitls.PojoConverSql;
import com.vf.admin.common.db.utils.PojoBySql;
import com.vf.admin.common.splitpage.SplitPageBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.*;

/**
 * 基本的DAO类
 * @param <T>
 */
public class BaseDao<T>{
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存
     */
    public int add(Object obj){
        List<Object> paramList = new ArrayList<Object>();
        try{
            PojoBySql pojobysql = PojoBySql.class.newInstance();
            PojoConverSql.getInstance().pojoBySql(obj, pojobysql);
            //拼接sql
            StringBuffer columnName = new StringBuffer();
            StringBuffer columnDataValue = new StringBuffer();
            for(Map.Entry<String, Object> entry: pojobysql.getColumnDateMap().entrySet()) {
                columnName.append(entry.getKey()+",");
                columnDataValue.append("?"+",");
            }
            for(Map.Entry<String, Object> entry: pojobysql.getColumnDateMap().entrySet()) {
                Object valueobj = pojobysql.getColumnDateMap().get(entry.getKey());
                paramList.add(valueobj);
            }

            String sql = "insert into "+pojobysql.getTableName() + "("+columnName.substring(0,columnName.length()-1)
                    + ") values(" + columnDataValue.substring(0,columnDataValue.length()-1)+ ")";


            Object[] objArray = null;
            if(paramList!=null && paramList.size()>0){
                objArray = new Object[paramList.size()];
                for(int i=0;i<paramList.size();i++){
                    objArray[i] = paramList.get(i);
                }
            }

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator(){
                                    @Override
                                    public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
                                        StringBuilder sqlLog = new StringBuilder("\r\n");
                                        PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                                        int j = 1;
                                        for(Map.Entry<String, Object> entry: pojobysql.getColumnDateMap().entrySet()) {
                                            Object valueobj = pojobysql.getColumnDateMap().get(entry.getKey());
                                            ps.setObject(j, valueobj);
                                            sqlLog.append(j+":"+valueobj+"  ");
                                            j++;
                                        }
                                        sqlLog.append(sql+"\r\n");
                                        logger.info(sqlLog.toString());
                                        return ps;
                                    }
                                },
                    keyHolder);
            return keyHolder.getKey().intValue();
        }catch(Exception e){
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 执行更新类的sql
     */
    public void update(String sql,final List<Object> paramList){
        try{
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator(){
                                    @Override
                                    public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
                                        StringBuilder sqlLog = new StringBuilder("\r\n");
                                        PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                                        Object[] objArray = null;
                                        if(paramList!=null && paramList.size()>0){
                                            objArray = new Object[paramList.size()];
                                            for(int i=0;i<paramList.size();i++){
                                                objArray[i] = paramList.get(i);
                                            }
                                            int j = 1;
                                            for(Object obj : objArray) {
                                                ps.setObject(j, obj);
                                                sqlLog.append(j+":"+obj+"\r\n");
                                                j++;
                                            }
                                        }


                                        sqlLog.append(sql+"\r\n");
//                                        sqlLog.append("**************** end ****************");
                                        logger.info(sqlLog.toString());
                                        return ps;
                                    }
                                },
                    keyHolder);
        }catch(Exception e){
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }


    /**
     * 更新信息
     * @throws SQLException
     */
    public void update(Object obj){
        try{
            PojoBySql pojobysql = PojoBySql.class.newInstance();
            PojoConverSql.getInstance().pojoBySql(obj, pojobysql);

            //拼接sql
            StringBuffer udpateNameBuffer = new StringBuffer();
            StringBuffer whereNameBuffer = new StringBuffer();


            if(pojobysql.getColumnDateMap()!=null && pojobysql.getColumnDateMap().size()>=1){
                for(Map.Entry<String, Object> entry: pojobysql.getColumnDateMap().entrySet()) {
                    if(pojobysql.getIndexName().equals(entry.getKey())){
                        continue;
                    }
                    udpateNameBuffer.append(entry.getKey()+" = ?  ,");
                }
                udpateNameBuffer = udpateNameBuffer.replace(udpateNameBuffer.length()-1, udpateNameBuffer.length(),"");
            }

            if(pojobysql.getIndexName()!=null && !pojobysql.getIndexName().equals("")){
                whereNameBuffer.append(" where ");
                whereNameBuffer.append(pojobysql.getIndexName()+" = ?  ,");
                whereNameBuffer = whereNameBuffer.replace(whereNameBuffer.length()-1, whereNameBuffer.length(),"");
            }
            String sql = "update  "+pojobysql.getTableName() + " set "+udpateNameBuffer + whereNameBuffer;


            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator(){
                                    @Override
                                    public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
                                        StringBuilder sqlLog = new StringBuilder("\r\n");
                                        PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                                        int j = 1;
                                        if(pojobysql.getColumnDateMap()!=null && pojobysql.getColumnDateMap().size()>=1){
                                            for(Map.Entry<String, Object> entry: pojobysql.getColumnDateMap().entrySet()) {
                                                if(pojobysql.getIndexName().equals(entry.getKey())){
                                                    continue;
                                                }
                                                Object valueobj = pojobysql.getColumnDateMap().get(entry.getKey());
                                                ps.setObject(j, valueobj);
                                                sqlLog.append(j+":"+valueobj+"\r\n");
                                                j++;
                                            }
                                        }
                                        if(pojobysql.getIndexName()!=null && !pojobysql.getIndexName().equals("")){
                                            ps.setObject(j, pojobysql.getColumnDateMap().get(pojobysql.getIndexName()));
                                            sqlLog.append(j+":"+pojobysql.getColumnDateMap().get(pojobysql.getIndexName())+"\r\n");
                                        }
                                        sqlLog.append(sql+"\r\n");
                                        logger.info(sqlLog.toString());
                                        return ps;
                                    }
                                },
                    keyHolder);
        }catch(Exception e){
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新信息
     * @throws SQLException
     */
    public void del(String sql,ArrayList<Object> paramList) {
        try{
            jdbcTemplate.update(new PreparedStatementCreator(){
                @Override
                public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{
                    StringBuilder sqlLog = new StringBuilder("\r\n");
                    PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                    int j = 1;
                    if(paramList!=null){
                        for(Object param : paramList) {
                            ps.setObject(j, param);
                            sqlLog.append(j+":"+param+"\r\n");
                            j++;
                        }
                    }
                    sqlLog.append(sql+"\r\n");
                    logger.info(sqlLog.toString());
                    return ps;
                }
            });
        }catch(Exception e){
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询分页总数
     */
    public Integer queryListCount(String sql,List<Object> paramList){
        try{
            StringBuilder sqlLog = new StringBuilder("\r\n");
            Object[] objArray = null;
            if(paramList!=null && paramList.size()>0){
                objArray = new Object[paramList.size()];
                for(int i=0;i<paramList.size();i++){
                    Object obj = paramList.get(i);
                    objArray[i] = obj;
                    sqlLog.append(i+":"+obj+"\r\n");
                }
            }
            sqlLog.append(sql+"\r\n");
            logger.info(sqlLog.toString());
            return this.jdbcTemplate.queryForObject(sql, objArray, Integer.class);
        }catch(Exception e){
            if(e.getClass().getName().equals(EmptyResultDataAccessException.class.getName())){    //查询结果为空
                return null;
            }
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询分页总数
     */
    public Long queryListCountLong(String sql, List<Object> paramList) {
        try {
            StringBuilder sqlLog = new StringBuilder("\r\n");
            Object[] objArray = null;
            if (paramList != null && paramList.size() > 0) {
                objArray = new Object[paramList.size()];
                for (int i = 0; i < paramList.size(); i++) {
                    Object obj = paramList.get(i);
                    objArray[i] = obj;
                    sqlLog.append(i + ":" + obj + "\r\n");
                }
            }
            sqlLog.append(sql + "\r\n");
            logger.info(sqlLog.toString());
            return this.jdbcTemplate.queryForObject(sql, objArray, Long.class);
        } catch (Exception e) {
            if (e.getClass().getName().equals(EmptyResultDataAccessException.class.getName())) {    //查询结果为空
                return 0l;
            }
            logger.error("", e);
            throw new RuntimeException(e);
        }
    }




    /**
     * 查询集合
     */
    public <T> List<T> queryList(String sql,List<Object> paramList,Class<T> c, SplitPageBean splitPage){
        try{
            StringBuilder sqlLog = new StringBuilder("\r\n");
            if(splitPage!=null){
                StringBuilder subSql = new StringBuilder("select count(*)  from (" + sql + ") as id");
                int count = this.queryListCount(subSql.toString(), paramList);


                // 将总记录数赋值给splitPageBean对象
                if((int)count > 0){
                    splitPage.setTotalCount((int)count);
                }else{
                    splitPage.setTotalCount(0);
                }

                sql = sql + " limit "+splitPage.getFirstRecord()+ " , "+splitPage.getMaxRecord();
            }

            Object[] objArray = null;
            if(paramList!=null && paramList.size()>0){
                objArray = new Object[paramList.size()];
                for(int i=0;i<paramList.size();i++){
                    Object obj = paramList.get(i);
                    objArray[i] = obj;
                    sqlLog.append(i+":"+obj+"\r\n");
                }
            }
            sqlLog.append(sql+"\r\n");
            logger.info(sqlLog.toString());
            List objList = jdbcTemplate.query(sql, objArray,new BeanPropertyRowMapper(c){
                @Override
                public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnLenth = rsmd.getColumnCount();
                    Map<String,Object> map = new HashMap<String,Object>();
                    for (int i = 1; i <= columnLenth; i++) {
                        map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                    }
                    String dataConent = JSON.toJSONStringWithDateFormat(map, "yyyy-MM-dd HH:mm:ss", SerializerFeature.WriteDateUseDateFormat);

                    Object rowObj = null;
                    try{
                        rowObj = JSON.parseObject(dataConent, c);
                    }catch(Exception e){
                        logger.info("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                        throw new RuntimeException("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                    }
                    return rowObj;
                }
            });
            return objList == null ? new ArrayList() : objList;
        }catch(Exception e){
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询集合
     */
    public <T> List<T> queryList(String sql,List<Object> paramList,Class<T> c){
        try{
            StringBuilder sqlLog = new StringBuilder("\r\n");
            Object[] objArray = null;
            if(paramList!=null && paramList.size()>0){
                objArray = new Object[paramList.size()];
                for(int i=0;i<paramList.size();i++){
                    Object obj = paramList.get(i);
                    objArray[i] = obj;
                    sqlLog.append("["+i+":"+obj+"]\r\n");
                }
            }
            sqlLog.append(sql+"\r\n");
//            sqlLog.append("**************** end ****************");
            logger.info(sqlLog.toString());
            List objList = jdbcTemplate.query(sql, objArray,new BeanPropertyRowMapper(c){
                        @Override
                        public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
                            ResultSetMetaData rsmd = rs.getMetaData();
                            int columnLenth = rsmd.getColumnCount();

                            Map<String,Object> map = new HashMap<String,Object>();
                            for (int i = 1; i <= columnLenth; i++) {
                                map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                            }
                            String dataConent = JSON.toJSONStringWithDateFormat(map, "yyyy-MM-dd HH:mm:ss", SerializerFeature.WriteDateUseDateFormat);

                            Object rowObj = null;
                            try{
                                rowObj = JSON.parseObject(dataConent, c);
                            }catch(Exception e){
                                logger.info("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                                throw new RuntimeException("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                            }
                            return rowObj;
                        }
                    }
            );
            if (objList == null){
//                System.out.println("查询的集合为空了");
            }else{
                if (objList.size() == 0){
//                    System.out.println("查询的集合长度Wie0");
                }
            }
            return objList == null ? new ArrayList() : objList;
        }catch(Exception e){
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * java.lang.Integer
     */
    private boolean isBaseDateType(Class clas){
        if(clas.getName().equals(Integer.class.getName())){
            return true;
        }else if(clas.getName().equals(Integer.class.getName())){
            return true;
        }else if(clas.getName().equals(Long.class.getName())){
            return true;
        }else if(clas.getName().equals(Double.class.getName())){
            return true;
        }else if(clas.getName().equals(Float.class.getName())){
            return true;
        }else if(clas.getName().equals(Character.class.getName())){
            return true;
        }else if(clas.getName().equals(Byte.class.getName())){
            return true;
        }else if(clas.getName().equals(Boolean.class.getName())){
            return true;
        }else if(clas.getName().equals(Short.class.getName())){
            return true;
        }
        return false;
    }
    /**
     * 查询对象
     */
    public <T> T  queryObj(String sql,List<Object> paramList,Class<T> c){
        try {
            StringBuilder sqlLog = new StringBuilder("\r\n");
            Object[] objArray = null;
            if(paramList!=null && paramList.size()>0){
                objArray = new Object[paramList.size()];
                for(int i=0;i<paramList.size();i++){
                    Object obj = paramList.get(i);
                    objArray[i] = obj;
                    sqlLog.append(i+":"+obj+"\r\n");
                }
            }
            sqlLog.append(sql+"\r\n");
//
            logger.info(sqlLog.toString());
            Object object = jdbcTemplate.queryForObject(sql, objArray,new BeanPropertyRowMapper(c){
                @Override
                public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnLenth = rsmd.getColumnCount();
                    Object rowObj = null;
                    //非基本类型
                    if(!isBaseDateType(c)){
                        Map<String,Object> map = new HashMap<String,Object>();
                        for (int i = 1; i <= columnLenth; i++) {
                            map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                        }
                        String dataConent = JSON.toJSONStringWithDateFormat(map, "yyyy-MM-dd HH:mm:ss", SerializerFeature.WriteDateUseDateFormat);
                        try{
                            rowObj = JSON.parseObject(dataConent, c);
                        }catch(Exception e){
                            logger.info("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                            throw new RuntimeException("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                        }

                    }else{
                        rowObj = rs.getObject(1);
                    }
                    return rowObj;
                }
            });
            object = object == null ? c.newInstance() : object;
            return (T)object;
        }catch(Exception e){
            if(e.getClass().getName().equals(EmptyResultDataAccessException.class.getName())){    //查询结果为空
                return null;
            }
            logger.error("",e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询对象
     */
    public <T> T  get(Object id,Class<T> c){
        try{
            StringBuilder sqlLog = new StringBuilder("\r\n");

            List<Object> paramList = new ArrayList<Object>();
            PojoBySql pojobysql = PojoBySql.class.newInstance();
            PojoConverSql.getInstance().pojoBySql(c, pojobysql);
            paramList.add(id);
            for(Map.Entry<String, Object> entry: pojobysql.getColumnDateMap().entrySet()) {
                Object valueobj = pojobysql.getColumnDateMap().get(entry.getKey());
                paramList.add(valueobj);
            }

            String sql = "select * from "+pojobysql.getTableName() + " where "+(StringUtils.isEmpty(pojobysql.getIndexName()) ? "id" : pojobysql.getIndexName())+" = ? ";
            Object[] objArray = new Object[1];
            objArray[0] = id;
            sqlLog.append(0+":"+id+"\r\n");
            sqlLog.append(sql+"\r\n");
//            sqlLog.append("**************** end ****************");
            logger.info(sqlLog.toString());

            Object object = jdbcTemplate.queryForObject(sql, objArray,new BeanPropertyRowMapper(c){
                @Override
                public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnLenth = rsmd.getColumnCount();

                    Map<String,Object> map = new HashMap<String,Object>();
                    for (int i = 1; i <= columnLenth; i++) {
                        map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                    }
                    String dataConent = JSON.toJSONStringWithDateFormat(map, "yyyy-MM-dd HH:mm:ss", SerializerFeature.WriteDateUseDateFormat);

                    Object rowObj = null;
                    try{
                        rowObj = JSON.parseObject(dataConent, c);
                    }catch(Exception e){
                        logger.info("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                        throw new RuntimeException("类型转换一场异常，请检查类属性与数据表的属性是否一致:" + e);
                    }

                    return rowObj;

                }
            });
            object = object == null ? c.newInstance() : object;
            return (T)object;

        }catch(Exception e){
            if(e.getClass().getName().equals(EmptyResultDataAccessException.class.getName())){    //查询结果为空
                return null;
            }
            throw new RuntimeException(e);
        }

    }

    /**
     * 查询分页总数
     */
    public Integer queryListCount(String sql, ArrayList<Object> paramList){
        try{
            Object[] objArray = null;
            if(paramList!=null && paramList.size()>0){
                objArray = new Object[paramList.size()];
                for(int i=0;i<paramList.size();i++){
                    Object obj = paramList.get(i);
                    objArray[i] = obj;
                }
            }
            return jdbcTemplate.queryForObject(sql, objArray,Integer.class);
        }catch(Exception e){
            if(e.getClass().getName().equals(EmptyResultDataAccessException.class.getName())){    //查询结果为空
                return null;
            }
            throw new RuntimeException(e);
        }


    }
}
